﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using TM.Common.Extensions;
using TM.Data.Contracts;
using TM.DataAccessor;
using TM.ExceptionManager;
using TM.Model;

namespace TM.Data
{
    public class ProjectTaskRepository : IProjectTaskRepository
    {
        private IDataAccessor _dataAccessor;
        private ITMExceptionHandler _exceptionHandler;

        public ProjectTaskRepository(IIoCParams ioCParams)
        {
            _dataAccessor = ioCParams.DataAccessor;
            _exceptionHandler = ioCParams.ExceptionHandler;
#if DEBUG
            _exceptionHandler.Throwable = true;
#endif
        }

        #region Implementation of IRepository<ProjectTask>

        public IQueryable<ProjectTask> GetAll()
        {
            return MapperFactories.GetMapper<ProjectTask>().Map(GetAllAsDataTable()).AsQueryable();
        }

        public DataTable GetAllAsDataTable()
        {
            _dataAccessor.Command.CommandType = CommandType.Text;
            _dataAccessor.Command.CommandText =
                "SELECT [ID],[Name], [DescDetails],[TypeID],[StatusID],[PriorityID],[OwnerID], " +
                "[RawEstimation], [ActualEstimation], " +
                "datetime([StartDate], 'unixepoch') AS StartDate, " +
                "[SpentTime], [TimeToComplete], datetime([EndDate], 'unixepoch') AS EndDate, " +
                "datetime([CreatedDate], 'unixepoch') AS CreatedDate, " +
                "[ParentID],[Resources],[Scopes]," +
                "[Evidents], [Methology], [ProjectID] FROM ProjectTask";
            var dt = _dataAccessor.ExecuteReader();
            return dt;
        }

        public ProjectTask GetById(Guid id)
        {
            return MapperFactories.GetMapper<ProjectTask>().Map(GetByIdAsDataTable(id)).AsQueryable().FirstOrDefault();
        }

        public DataTable GetByIdAsDataTable(Guid id)
        {
            _dataAccessor.Command.CommandType = CommandType.Text;
            _dataAccessor.Command.CommandText = string.Format("SELECT [ID],[Name], [DescDetails],[TypeID],[StatusID],[PriorityID],[OwnerID], " +
                "[RawEstimation], [ActualEstimation], " +
                "datetime([StartDate], 'unixepoch') AS StartDate, " +
                "[SpentTime], [TimeToComplete], datetime([EndDate], 'unixepoch') AS EndDate, " +
                "datetime([CreatedDate], 'unixepoch') AS CreatedDate, " +
                "[ParentID],[Resources],[Scopes]," +
                "[Evidents], [Methology], [ProjectID] FROM ProjectTask WHERE UPPER([ID]) = UPPER('{0}')", id.ToString());
            var dt = _dataAccessor.ExecuteReader();
            return dt;
        }

        public IQueryable<ProjectTask> GetList(Func<ProjectTask, bool> predicate)
        {
            var data = GetAll();
            return from item in data where predicate(item) select item;
        }

        public ProjectTask GetSingle(Func<ProjectTask, bool> predicate)
        {
            var data = GetAll();
            return (from item in data select item).Single(predicate);
        }

        public ProjectTask GetFisrt(Func<ProjectTask, bool> predicate)
        {
            var data = GetAll();
            return (from item in data select item).First(predicate);
        }

        public bool Insert(ProjectTask entity)
        {
            try
            {
                _dataAccessor.Command.CommandType = CommandType.Text;
                _dataAccessor.Command.CommandText =
                    string.Format("INSERT INTO ProjectTask([ID],[Name], [DescDetails],[TypeID],[StatusID],[PriorityID],[OwnerID], " +
                    "[RawEstimation], [ActualEstimation], " +
                    "[StartDate], " +
                    "[SpentTime], [TimeToComplete], [EndDate], " +
                    "[CreatedDate], " +
                    "[ParentID],[Resources],[Scopes]," +
                    "[Evidents], [Methology], [ProjectID]) VALUES('{0}','{1}','{2}','{3}', '{4}' , '{5}' ,'{6}', {7} ,{8}, {9} ,{10}, " +
                    "{11},{12},{13},'{14}','{15}','{16}','{17}','{18}','{19}')",
                    entity.ID, entity.Name, entity.DescDetails,
                    entity.TypeID, entity.StatusID, entity.PriorityID, entity.OwnerID,
                    entity.RawEstimation.ToSQLiteNumber(), entity.ActualEstimation.ToSQLiteNumber(), entity.StartDate.ToSQLiteDateTime(),
                    entity.SpentTime.ToSQLiteNumber(), entity.TimeToComplete.ToSQLiteNumber(), entity.EndDate.ToSQLiteDateTime(),
                    DateTime.Now.ToSQLiteDateTime(), entity.ParentID, entity.Resources,
                    entity.Scopes, entity.Evidents, entity.Methology, entity.ProjectID);

                var rt = _dataAccessor.ExecuteNonQuery();

                return (rt > 0);
            }
            catch (Exception exception)
            {
                _exceptionHandler.Handle(exception, "Insert ProjectTask item");
            }
            return false;
        }

        public bool Insert(IList<ProjectTask> entities)
        {
            var trans = _dataAccessor.BeginTransaction("InsertProjectTasks");

            try
            {
                foreach (var entity in entities)
                {
                    if (GetById(entity.ID) == null)
                    {
                        Insert(entity);
                    }
                    else
                    {
                        Update(entity);
                    }
                }
                _dataAccessor.EndTransaction(trans, true);
            }
            catch (Exception exception)
            {
                _dataAccessor.EndTransaction(trans, false);
                _exceptionHandler.Handle(exception, "Insert list of ProjectTasks");
            }

            return false;
        }

        public bool Update(ProjectTask entity)
        {
            try
            {
                _dataAccessor.Command.CommandType = CommandType.Text;
                _dataAccessor.Command.CommandText =
                    string.Format("UPDATE ProjectTask SET [Name]='{1}', [DescDetails]='{2}',[TypeID]= '{3}', " +
                    "[StatusID]= '{4}',[PriorityID]= '{5}',[OwnerID]= '{6}', " +
                    "[RawEstimation]={7}, [ActualEstimation]={8}, " +
                    "[StartDate]={9}, [SpentTime]={10}, [TimeToComplete]={11}, [EndDate]={12}, " +
                    "[ParentID]= '{14}',[Resources]='{15}',[Scopes]='{16}'," +
                    "[Evidents]='{17}', [Methology]='{18}', [ProjectID]= '{19}' WHERE UPPER([ID]) = UPPER('{0}')",
                    entity.ID, entity.Name, entity.DescDetails,
                    entity.TypeID, entity.StatusID, entity.PriorityID, entity.OwnerID,
                    entity.RawEstimation.ToSQLiteNumber(), entity.ActualEstimation.ToSQLiteNumber(), entity.StartDate.ToSQLiteDateTime(),
                    entity.SpentTime.ToSQLiteNumber(), entity.TimeToComplete.ToSQLiteNumber(), entity.EndDate.ToSQLiteDateTime(),
                    entity.CreatedDate.ToSQLiteDateTime(), entity.ParentID, entity.Resources,
                    entity.Scopes, entity.Evidents, entity.Methology, entity.ProjectID);

                var rt = _dataAccessor.ExecuteNonQuery();

                return (rt > 0);
            }
            catch (Exception exception)
            {
                _exceptionHandler.Handle(exception, "Update ProjectTask item");
            }
            return false;
        }

        public bool Update(IList<ProjectTask> entities)
        {
            var trans = _dataAccessor.BeginTransaction("UpdateProjectTask");
            
            var alltasks = GetAllOfProject(entities[0].ProjectID);
            
            try
            {
                //Insert or update all submitted items
                foreach (var entity in entities)
                {
                    if (GetById(entity.ID) == null)
                    {
                        Insert(entity);
                    }
                    else
                    {
                        Update(entity);
                    }
                }

                //Delete all items does not exist in the list submitted
                foreach (var projectTask in alltasks)
                {
                    var existing = entities.FirstOrDefault(item => item.ID == projectTask.ID);
                    if (existing == null)
                    {
                        Delete(projectTask);
                    }
                }

                _dataAccessor.EndTransaction(trans, true);
                return true;
            }
            catch (Exception exception)
            {
                _dataAccessor.EndTransaction(trans, false);
                _exceptionHandler.Handle(exception, "Update list of ProjectTasks");
            }

            return false;
        }

        public bool Delete(ProjectTask entity)
        {
            try
            {
                _dataAccessor.Command.CommandType = CommandType.Text;
                _dataAccessor.Command.CommandText =
                    string.Format("DELETE FROM ProjectTask WHERE UPPER([ID]) = UPPER('{0}')",
                                  entity.ID);

                var rt = _dataAccessor.ExecuteNonQuery();

                return (rt > 0);
            }
            catch (Exception exception)
            {
                _exceptionHandler.Handle(exception, "Delete ProjectTask item");
            }
            return false;
        }

        public bool Delete(IList<ProjectTask> entities)
        {
            var trans = _dataAccessor.BeginTransaction("DeleteProjects");
            try
            {
                foreach (var entity in entities)
                {
                    _dataAccessor.Command.CommandType = CommandType.Text;
                    _dataAccessor.Command.CommandText =
                        string.Format("DELETE FROM ProjectTask WHERE UPPER([ID]) = UPPER('{0}')",
                                      entity.ID);

                    var rt = _dataAccessor.ExecuteNonQuery();
                    if (!(rt > 0))
                    {
                        _dataAccessor.EndTransaction(trans, false);
                    }
                }
                _dataAccessor.EndTransaction(trans, true);
            }
            catch (Exception exception)
            {
                _dataAccessor.EndTransaction(trans, false);
                _exceptionHandler.Handle(exception, "Delete list ProjectTask items");
            }
            return false;
        }

        public bool Delete(Guid id)
        {
            try
            {
                _dataAccessor.Command.CommandType = CommandType.Text;
                _dataAccessor.Command.CommandText =
                    string.Format("DELETE FROM ProjectTask WHERE UPPER([ID]) = UPPER('{0}')",
                                  id);

                var rt = _dataAccessor.ExecuteNonQuery();

                return (rt > 0);
            }
            catch (Exception exception)
            {
                _exceptionHandler.Handle(exception, "Delete ProjectTask item");
            }
            return false;
        }

        public bool Delete(Func<ProjectTask, bool> predicate)
        {
            var items = GetList(predicate);
            return Delete(items.ToList());
        }

        #endregion

        #region Implementation of IProjectTaskRepository

        public IQueryable<ProjectTask> GetAllOfProject(Guid projectId)
        {
            var allItems = GetAll();

            return allItems;
            //var item = new ProjectTask()
            //           {
            //               ID = 1,
            //               ActualEstimation = 3,
            //               CreatedDate = DateTime.Now,
            //               DescDetails = "Thoong tin chi tiet",
            //               EndDate = DateTime.Now,
            //               Evidents = "1;2",
            //               Resources = "1;2",
            //               Scopes = "1;2",
            //               TypeID = 1,
            //               StatusID = 1,
            //               PriorityID = 1,
            //               RawEstimation = 2,
            //               SpentTime = 0,
            //               Methology = "Phuong thuc trien khai cong viec",
            //               Name = "Tao cau truc tai lieu o muc high level",
            //               OwnerID = 1
            //           };

            //return new List<ProjectTask> {item}.AsQueryable();
        }

        public IQueryable<ProjectTask> GetAllOfTheOwner(int ownerId, Guid projectId)
        {
            throw new NotImplementedException();
        }

        public IQueryable<ProjectTask> GetAllOfTheOwner(int ownerId, Guid projectId, DateTime startDate)
        {
            throw new NotImplementedException();
        }

        public IQueryable<ProjectTask> GetAllOfTheOwnerByCreationDate(int ownerId, Guid projectId, DateTime createdDate)
        {
            throw new NotImplementedException();
        }

        public IQueryable<ProjectTask> GetAllOfTheOwnerByEndDate(int ownerId, Guid projectId, DateTime actualStartDate)
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}
